import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

/**
 * Handles operation with the Database
 * @author Emiliano Marin
 * @version 1.0
 * @since 1.0
 */
public class MagazzinoDB{
	
    private static String oggi;
    private static String database = "magazzino.db";

    /**
     * Opens a connection to SQLite database and sets AutoCommit to true
     * @return A Connection object 
     */
    private static Connection getConnection () {
    	oggi = getOggi();
    	Connection conn=null;
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:"+database);
			conn.setAutoCommit(true);
		} catch (ClassNotFoundException e) {
			closeConnection(conn);
			System.out.println(e.getMessage());
			System.exit(0);
		} catch (SQLException e) {
			closeConnection(conn);
			System.out.println(e.getMessage());
			System.exit(0);
		}
		return conn;
    }
    
    public static void setDatabase (String db) { 
    	database = db;
    }
    
    /**
     * Close the connection with the SQLite database
     * @param conn An open Connection object
     */
    private static void closeConnection(Connection conn) {
    	try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	
    }
    
    /**
     * Formats the today's date as yyyy-MM-dd for further use in SQL statements
     * @return A String representing today's date as yyyy-MM-dd
     */
	private static String getOggi() {
		DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date date = new Date();
        return dateFormat.format(date);
	}

	/**
	 * Checks whether inserting or updating a product in the database 
	 * @param codice A String representing the code of the product
	 * @param nome A String representing the name of the product
	 * @param quantita An integer representing the quantity to load in the database 
	 * @param taglia A String representing the size or special characteristic of the product
	 * @param prezzo A float representing the price of the product
	 * @return True if the operation was successful, False otherwise
	 */
	public static boolean inserisciArticolo (String codice, String nome, int quantita, String taglia, float prezzo) {
		if (controllaEsistenzaArticolo(codice, taglia)) {
			return aggiornaMagazzino(codice, quantita, taglia, prezzo);
		} else {
			return inserisciMagazzino(codice, nome, quantita, taglia, prezzo);
		}
	}
	
	/**
	 * Inserts a new article in the database's table prodotto
	 * @param codice A String representing the code of the product
	 * @param nome A String representing the name of the product
	 * @param quantita An integer representing the quantity to load in the database 
	 * @param taglia A String representing the size or special characteristic of the product
	 * @param prezzo A float representing the price of the product
	 * @return True if the operation was successful, False otherwise
	 */
	public static boolean inserisciMagazzino(String codice, String nome, int quantita, String taglia, float prezzo) {
		Connection conn = getConnection();
		try {
			
			Statement statement = conn.createStatement();
			String s = "INSERT INTO prodotto VALUES ('" + codice + "', " 
				+ "'" + nome + "'," 
				+ quantita + ","
				+ "'" + taglia + "'," 
				+ "'" + oggi + "',"
				+ "'" + oggi + "',"
				+ prezzo + ");"; 
			statement.executeUpdate(s);
			closeConnection(conn);
		} catch (SQLException e) {
			closeConnection(conn);
			System.out.println(e.getMessage());
			return false;
		}
		
		return true;
	}
	
	/**
	 * Updates quantita (add), price and DataAggiornamento of an existing article in the database's table prodotto 
	 * @param codice A String representing the code of the product
	 * @param quantita An integer representing the quantity to add in the database
	 * @param taglia A String representing the size or special characteristic of the product
	 * @param prezzo A float representing the price of the product
	 * @return True if the operation was successful, False otherwise
	 */
	public static boolean aggiornaMagazzino (String codice, int quantita, String taglia, float prezzo) {
		Connection conn = getConnection();
		try {
			Statement statement = conn.createStatement();
			//Aggiorna la quantita' in magazzino
			String s = "UPDATE prodotto SET quantita = " + quantita + " WHERE codice = '" + codice + "' and taglia = '" + taglia + "';";
			statement.executeUpdate(s);
			//Aggiorna il prezzo del prodotto
			s = "UPDATE prodotto SET prezzo =  " + prezzo + " WHERE codice = '" + codice + "' and taglia = '" + taglia + "';";
			statement.executeUpdate(s);
			//Aggiorna data ultimo aggiornamento prodotto 
			s = "UPDATE prodotto SET DataAggiornamento =  '" + oggi + "' WHERE codice = '" + codice + "' and taglia = '" + taglia + "';";
			statement.executeUpdate(s);
			closeConnection(conn);
		} catch (SQLException e) {
			closeConnection(conn);
			System.out.println(e.getMessage());
			return false;
		}
		return true;
	}
	
	/**
	 * Checks whether a product is already in the database's table prodotto
	 * A procuts is defined as combination of code and size/characteristic
	 * @param codice A String representing the code of the product
	 * @param taglia A String representing the size or special characteristic of the product
	 * @return True if the product exists, False otherwise
	 */
	public static boolean controllaEsistenzaArticolo (String codice, String taglia) { 
		String s = "SELECT * FROM prodotto WHERE codice = '" + codice + "' AND taglia = '" + taglia + "'";
		int i = 0;
		Connection conn = getConnection();
		try {
			Statement statement = conn.createStatement();
			ResultSet resultSet;
			
			resultSet = statement.executeQuery(s);
			
			while(resultSet.next()) { 
				i++;
			}			
			resultSet.close();
			closeConnection(conn);
		} catch (SQLException e) {
			closeConnection(conn);
			return false;
		}
		if (i>0) 
			return true;
		else 
			return false;
		
		
	}
	
	/**
	 * Counts how many times a product's code is in the database's table prodotto
	 * @param codice A String representing the code of the product
	 * @return An Integer representing the number of times a product's code appears (>= 0) 
	 */
	public static int codiceEsiste (String codice) {
		int r=0;
		String s = "SELECT COUNT(codice) AS art_no FROM prodotto WHERE codice = '" + codice + "';";
		Connection conn = getConnection();
		try {
			
			Statement statement = conn.createStatement();
			ResultSet resultSet;
			
			resultSet = statement.executeQuery(s);
			while(resultSet.next()) {
				r = resultSet.getInt("art_no");
			}
			resultSet.close();
			closeConnection(conn);
		} catch (SQLException e) { 
			closeConnection(conn);
			System.out.println(e.getMessage());
			System.exit(0);
		}
		return r;
	}
	
	public static int nomeEsiste (String nome) { 
		int r = 0;
		String s = "SELECT COUNT(nome) AS nome_no FROM prodotto WHERE nome LIKE '" + nome + "';";
		Connection conn = getConnection();
		try { 
			Statement statement = conn.createStatement();
			ResultSet resultSet;
			
			resultSet = statement.executeQuery(s);
			while(resultSet.next()) { 
				r = resultSet.getInt("nome_no");
			}
			resultSet.close();
			closeConnection(conn);
		} catch (SQLException e ) { 
			closeConnection(conn);
			System.out.println(e.getMessage());
			System.exit(0);
		}
		return r;
	}
	
	/**
	 * Retrieves the name associated with a product's code
	 * @param codice A String representing the code of the product
	 * @return A String representing the name of the product. Returns null if the article is not in the database's table prodotto.
	 */
	public static String getNome (String codice) { 
		String s = "SELECT nome FROM prodotto WHERE codice = '" + codice + "' GROUP BY nome;";
		String nome = null;
		Connection conn = getConnection();
		try { 
			
			Statement statement = conn.createStatement();
			ResultSet resultSet;
			
			resultSet = statement.executeQuery(s);
			while(resultSet.next()) {
				nome= resultSet.getString("nome");
			}
			resultSet.close();
			closeConnection(conn);
		} catch (SQLException e) {
			closeConnection(conn);
			System.out.println(e.getMessage());
			System.exit(0);
		}
		return nome;
	}
	
	/**
	 * Retrieves the price associated to a product
	 * @param codice A String representing the code of the product
	 * @param taglia A String representing the size or special characteristic of the product
	 * @return A String representing the price of the procuct. Returns null if the article is not in the database's table prodotto.
	 */
	public static float getPrezzo (String codice, String taglia) { 
		String s = "SELECT prezzo FROM prodotto WHERE codice = '" + codice + "' AND taglia = '" + taglia + "';";
		float prezzo= -1;
		Connection conn = getConnection();
		try {
			
			Statement statement = conn.createStatement();
			ResultSet resultSet;
			
			resultSet = statement.executeQuery(s);
			while(resultSet.next()) { 
				prezzo =  resultSet.getFloat(1);
			}
			resultSet.close();
			closeConnection(conn);
		} catch (SQLException e) {
			closeConnection(conn);
			System.out.println(e.getMessage()); 
			System.exit(0);
		}
		return prezzo;
	}
	
	/**
	 * Retrieves the quantity of a product
	 * @param codice A string representing the code of the product
	 * @param taglia A string representing the variant of the product (size, color, etc)
	 * @return An integer representing the quantity available of the specified product. Return 0 also if the article is not available in the database.
	 */
	public static int getQuantita (String codice, String taglia) { 
		String s = "SELECT quantita FROM prodotto WHERE codice = '" + codice + "' AND taglia = '" + taglia + "';";
		int quantita= 0;
		Connection conn = getConnection();
		try {
			
			Statement statement = conn.createStatement();
			ResultSet resultSet;
			
			resultSet = statement.executeQuery(s);
			while(resultSet.next()) { 
				quantita =  resultSet.getInt(1);
			}
			resultSet.close();
			closeConnection(conn);
		} catch (SQLException e) {
			closeConnection(conn);
			System.out.println(e.getMessage()); 
			System.exit(0);
		}
		return quantita;
	}
	
	public static Object[][] ricercaPerNome (String nome) {
		
		String[] nomi = nome.split(" ");
		String condition = "";
		boolean first=true;
		for (String n : nomi) {
			if (first) {
				condition = "nome LIKE '%" + n + "%'";
				first=!first;
			} else { 
				condition = condition + " AND nome LIKE '%" + n + "%'"; 
			}
			
		}
		
		Connection conn = getConnection();
		String s = "SELECT COUNT(*) FROM prodotto WHERE " + condition + ";";
		int rows=0;
		try { 
			Statement statement = conn.createStatement();
			ResultSet resultSet; 
			
			resultSet = statement.executeQuery(s);
			
			resultSet.next(); 
			rows = resultSet.getInt(1);
			closeConnection(conn);
		} catch (SQLException e) { 
			closeConnection(conn); 
			System.out.println(e.getMessage());
			System.exit(0);
		}
		conn = getConnection();
		if (rows==0) { 
			return null;
		}
		
		Object[][] risultato = new Object[rows][5];
		
		s = "SELECT codice, nome, taglia, quantita, prezzo FROM prodotto WHERE " + condition + ";";
		try {
			
			Statement statement = conn.createStatement();
			ResultSet resultSet;
			
			resultSet = statement.executeQuery(s);
			int i = 0;
			while(resultSet.next()) {
				if (i < rows) { 
					risultato[i][0] = resultSet.getString(1);
					risultato[i][1] = resultSet.getString(2);
					risultato[i][2] = resultSet.getString(3);
					risultato[i][3] = resultSet.getInt(4);
					risultato[i][4] = resultSet.getFloat(5);
				}
				i++;
			}
			resultSet.close();
			closeConnection(conn);
		} catch (SQLException e) {
			closeConnection(conn);
			System.out.println(e.getMessage()); 
			System.exit(0);
		}
		return risultato;
	}
	
	public static Object[][] ricercaPerCodice (String codice) {		
		Connection conn = getConnection();
		String s = "SELECT COUNT(*) FROM prodotto WHERE codice = '" + codice + "';";
		int rows=0;
		try { 
			Statement statement = conn.createStatement();
			ResultSet resultSet; 
			
			resultSet = statement.executeQuery(s);
			
			resultSet.next(); 
			rows = resultSet.getInt(1);
			closeConnection(conn);
		} catch (SQLException e) { 
			closeConnection(conn); 
			System.out.println(e.getMessage());
			System.exit(0);
		}
		conn = getConnection();
		Object[][] risultato = new Object[rows][5];
		risultato[0][0] = " ";
		risultato[0][1] = " ";
		risultato[0][2] = " ";
		risultato[0][3] = 0;
		risultato[0][4] = 0.00;
		s = "SELECT codice, nome, taglia, quantita, prezzo FROM prodotto WHERE codice = '" + codice + "';";
		try {
			
			Statement statement = conn.createStatement();
			ResultSet resultSet;
			
			resultSet = statement.executeQuery(s);
			int i = 0;
			while(resultSet.next()) {
				if (i < rows) { 
					risultato[i][0] = resultSet.getString(1);
					risultato[i][1] = resultSet.getString(2);
					risultato[i][2] = resultSet.getString(3);
					risultato[i][3] = resultSet.getInt(4);
					risultato[i][4] = resultSet.getFloat(5);
				}
				i++;
			}
			resultSet.close();
			closeConnection(conn);
		} catch (SQLException e) {
			closeConnection(conn);
			System.out.println(e.getMessage()); 
			System.exit(0);
		}
		return risultato;
	}
	
	/**
	 * Retrieves all available size/characteristic of a product
	 * @param codice A String representing the code of the product
	 * @return An ArrayList of Strings containing all size/characteristics. Returns null if the article is not in the database's table prodotto.
	 */
	public static ArrayList<String> getTaglie (String codice) {
		String s = "SELECT Taglia FROM prodotto WHERE codice = '" + codice + "' GROUP BY Taglia ORDER BY Taglia ASC;";
		Connection conn = getConnection();
		try { 
			
			Statement statement = conn.createStatement();
			ResultSet resultSet;
			
			ArrayList<String> taglie = new ArrayList<String>();
			resultSet = statement.executeQuery(s);
			while (resultSet.next()) { 
				taglie.add(resultSet.getString("Taglia"));
			}
			resultSet.close();
			closeConnection(conn);
			
			return taglie;
		} catch (SQLException e) {
			closeConnection(conn);
			System.out.println(e.getMessage());
			System.exit(0);			
		}
		return null; 
	}
	
	
}
